from flask import Flask
import os
import sqlite3
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String, Boolean, DateTime, ForeignKey, create_engine)
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

app = Flask(__name__)

Base = declarative_base()





class User(Base):
    __tablename__ = "user"
    id = Column(String(20), primary_key=True)
    name = Column(String(20))


def test_tee():
    metadata = MetaData()
    cookies = Table('cookies', metadata,
                    Column('cookie_id', Integer(), primary_key=True),
                    Column('cookie_name', String(50), index=True),
                    Column('cookie_recipe_url', String(255)),
                    Column('cookie_sku', String(55)),
                    Column('quantity', Integer()),
                    Column('unit_cost', Numeric(12, 2))
                    )
    engine = create_engine("sqlite:///tes.db")
    metadata.create_all(engine)
    connect = engine.connect()
    ins = cookies.insert().values(
        cookie_name="chocolate chip2",
        cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
        cookie_sku="CC01",
        quantity="12",
        unit_cost="0.50"
    )
    result = connect.execute(ins)
    print(str(ins))
    print(result.inserted_primary_key)


@app.route('/')
def hello_world():
    test_tee()
    return 'Hello World!'


def opendb():
    connect = sqlite3.connect("test.db")
    cursor = connect.cursor()
    cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
    cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
    cursor.rowcount
    cursor.close()
    connect.commit()
    connect.close()


def insertdb():
    conn = sqlite3.connect("test.db")
    cursor = conn.cursor()
    cursor.execute(r"insert into users values ('A-001', 'Adam', 95)")
    cursor.execute(r"insert into users values ('A-002', 'Bart', 62)")
    cursor.execute(r"insert into users values ('A-003', 'Lisa', 78)")
    cursor.close()
    conn.commit()
    conn.close()


def searchwithscore():
    connect = sqlite3.connect("test.db")
    cursor = connect.cursor()
    cursor.execute('select * from users where id=?', ())
    values = cursor.fetchall()
    print(values)
    cursor.close()
    connect.close()


def searchdb():
    connect = sqlite3.connect("test.db")
    cursor = connect.cursor()
    cursor.execute('select * from user where id=?', ('1',))
    values = cursor.fetchall()
    print(values)
    cursor.close()
    connect.close()



if __name__ == '__main__':
    app.run()
